﻿using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;
using System;


namespace Shuba.Data
{
    public class Regional
    {
        public Regional()
        {
        }

        public RegionalInfo Get(string id)
        {
            RegionalInfo ri = new RegionalInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_REGIONAL WHERE REGIONAL_ID LIKE '' + @REGIONAL_ID + ''");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5));
                }
                dr.Close();
            }
            return ri;
        }

        public List<RegionalInfo> GetList(string value, string CostCenter, string Nip, string AppID)
        {
            List<RegionalInfo> list = new List<RegionalInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(
            @"DECLARE @PROCESS_DATE DATETIME, @COST_CENTER VARCHAR(6), @USER_TYPE VARCHAR(10) 
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

            SELECT TOP 1 @COST_CENTER = BRANCH_ID FROM V_USER 
            WHERE NIP = @NIP AND APP_ID = @APP_ID GROUP BY BRANCH_ID

            SELECT TOP 1 @USER_TYPE = USER_TYPE FROM V_USER
            WHERE NIP = @NIP AND APP_ID = @APP_ID GROUP BY USER_TYPE

            IF (@USER_TYPE = 'LA' OR @USER_TYPE = 'CO' OR @USER_TYPE = 'SO')
            BEGIN
                SELECT * FROM TBL_MS_REGIONAL 
                WHERE 
                    REGIONAL_ID IN 
                    (
                        SELECT B.REGIONAL_ID
                        FROM TBL_MS_CLUSTER_BRANCH A INNER JOIN TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID
                        INNER JOIN TBL_ALTERNATE_UM C ON A.BRANCH_ID = C.BRANCH_ID
                        INNER JOIN TBL_USER_TYPE_GROUP G ON C.ALTERNATE_FOR = G.USER_TYPE
                        WHERE 
                            (C.NIP = @NIP)
					        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),C.EFF_DATE_FROM,120)) 
					        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),C.EFF_DATE_TO,120)) 
					        AND C.IS_ACTIVE = '1'
                            AND G.APP_ID = @APP_ID
                        GROUP BY B.REGIONAL_ID
                    ) AND
                    REGIONAL_NAME LIKE '%' + @REGIONAL_NAME + '%' ORDER BY REGIONAL_NAME    
            END
            
            ELSE

            SELECT * FROM TBL_MS_REGIONAL 
            WHERE 
                REGIONAL_ID IN 
                (
                    SELECT B.REGIONAL_ID
                    FROM
                        TBL_MS_CLUSTER_BRANCH A INNER JOIN 
                        TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID LEFT JOIN
                        TBL_MS_HO_REGION C ON B.REGIONAL_ID = C.REGIONAL_ID
                    WHERE (C.HO_ID = @COST_CENTER) OR (B.REGIONAL_ID = @COST_CENTER) OR (A.CLUSTER_ID = @COST_CENTER) OR (A.BRANCH_ID = @COST_CENTER)
                    GROUP BY B.REGIONAL_ID
                    UNION
                    SELECT B.REGIONAL_ID
                    FROM TBL_MS_CLUSTER_BRANCH A INNER JOIN TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID
                    INNER JOIN TBL_ALTERNATE_UM C ON A.BRANCH_ID = C.BRANCH_ID
                    INNER JOIN TBL_USER_TYPE_GROUP G ON C.ALTERNATE_FOR = G.USER_TYPE
                    WHERE 
                        (C.NIP = @NIP)
					    AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),C.EFF_DATE_FROM,120)) 
					    AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),C.EFF_DATE_TO,120)) 
					    AND C.IS_ACTIVE = '1'
                        AND G.APP_ID = @APP_ID
                    GROUP BY B.REGIONAL_ID
                ) AND
                REGIONAL_NAME LIKE '%' + @REGIONAL_NAME + '%' ORDER BY REGIONAL_NAME"
            );
            db.AddInParameter(cmd, "@REGIONAL_NAME", DbType.String, value);
            db.AddInParameter(cmd, "@NIP", DbType.String, Nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<RegionalInfo> GetAll()
        {
            List<RegionalInfo> list = new List<RegionalInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_REGIONAL ORDER BY REGIONAL_NAME");

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<RegionalInfo> GetListByHO(string HO_ID)
        {
            List<RegionalInfo> list = new List<RegionalInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_REGIONAL
                                                    WHERE REGIONAL_ID IN 
	                                                    (
		                                                    SELECT REGIONAL_ID FROM TBL_MS_HO_REGION WHERE HO_ID = @HO_ID
	                                                    )
                                                    ORDER BY REGIONAL_NAME");
            db.AddInParameter(cmd, "@HO_ID", DbType.String, HO_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<RegionalInfo> GetListByCluster(string CLUSTER_ID)
        {
            List<RegionalInfo> list = new List<RegionalInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_REGIONAL
                                                    WHERE REGIONAL_ID IN 
                                                        (
                                                            SELECT REGIONAL_ID FROM TBL_MS_REGION_CLUSTER WHERE CLUSTER_ID = @CLUSTER_ID
                                                        )
                                                    ORDER BY REGIONAL_NAME");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, CLUSTER_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<RegionalInfo> GetListByUnit(string UNIT_CODE)
        {
            List<RegionalInfo> list = new List<RegionalInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_REGIONAL WHERE REGIONAL_ID IN
                                (
                                    SELECT A.REGIONAL_ID 
                                    FROM TBL_MS_REGION_CLUSTER AS A INNER JOIN TBL_MS_CLUSTER_BRANCH AS B 
                                    ON A.CLUSTER_ID = B.CLUSTER_ID
                                    WHERE (B.BRANCH_ID = @UNIT_CODE) GROUP BY A.REGIONAL_ID
                                ) ORDER BY REGIONAL_NAME");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }  

        public string GetRegionIDByUnit(string value)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.REGIONAL_ID 
                                FROM TBL_MS_REGION_CLUSTER AS A INNER JOIN TBL_MS_CLUSTER_BRANCH AS B 
                                ON A.CLUSTER_ID = B.CLUSTER_ID
                                WHERE (B.BRANCH_ID = @BRANCH_ID) GROUP BY A.REGIONAL_ID
                                ");
            object result;
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, value);
            result = db.ExecuteScalar(cmd);

            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return "";
            }
        }

        public List<RegionalInfo> GetListApproval(string value, string UpdateBy)
        {
            List<RegionalInfo> list = new List<RegionalInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_REGIONAL_TMP WHERE REGIONAL_NAME LIKE '%' + @REGIONAL_NAME + '%' AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '') ORDER BY REGIONAL_NAME");
            db.AddInParameter(cmd, "@REGIONAL_NAME", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6)));
                }
                dr.Close();
            }
            return list;
        }

        public List<RegionalInfo> GetDependency(string RegionalID)
        {
            List<RegionalInfo> list = new List<RegionalInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT DISTINCT * FROM 
                                (
            	                    SELECT A.* FROM TBL_MS_REGIONAL A INNER JOIN TBL_MS_REGION_CLUSTER B ON A.REGIONAL_ID = B.REGIONAL_ID
            	                    WHERE A.REGIONAL_ID LIKE '' + @REGIONAL_ID + ''
                                    UNION
            	                    SELECT A.* FROM TBL_MS_REGIONAL A INNER JOIN TBL_REGION_TARGET_PLAN B ON A.REGIONAL_ID = B.REGIONAL_ID
            	                    WHERE A.REGIONAL_ID LIKE '' + @REGIONAL_ID + ''
            
                                )X");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }    

        public RegionalInfo GetApprovalByID(string id)
        {
            RegionalInfo ri = new RegionalInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_REGIONAL_TMP WHERE REGIONAL_ID LIKE '' + @REGIONAL_ID + ''");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new RegionalInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6));
                }
                dr.Close();
            }

            return ri;
        }

        public void Save(RegionalInfo regional)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = @"
                                UPDATE [TBL_MS_REGIONAL_TMP]
                                   SET [REGIONAL_NAME] = @REGIONAL_NAME
                                      ,[ENTRY_BY] = @ENTRY_BY
                                      ,[ENTRY_DATE] = @ENTRY_DATE
                                      ,[UPDATE_BY] = @UPDATE_BY
                                      ,[UPDATE_DATE] = @UPDATE_DATE
                                      ,[ACT_TYPE] = @ACT_TYPE
                                WHERE REGIONAL_ID LIKE '' + @REGIONAL_ID + '';
                                IF @@ROWCOUNT = 0
                                INSERT INTO [TBL_MS_REGIONAL_TMP] ([REGIONAL_ID],[REGIONAL_NAME],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[ACT_TYPE]) " +
                              " VALUES (@REGIONAL_ID, @REGIONAL_NAME, @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE)";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, regional.RegionalID);
            db.AddInParameter(cmd, "@REGIONAL_NAME", DbType.String, regional.RegionalName);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, regional.EntryBy);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, regional.EntryDate);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, regional.UpdateBy);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, regional.UpdateDate);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, regional.ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Approve(string RegionalId, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("REGIONAL_APPROVAL"); 

            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalId);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string regionalId)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = String.Format("DELETE FROM TBL_MS_REGIONAL_TMP WHERE REGIONAL_ID LIKE '{0}'", regionalId);
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.ExecuteNonQuery(cmd);
        }

    }
}